﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using RN.ERP.Entity;
using RN.ERP.Entity.ZBDLRY;
using RN.ERP.Entity.ResultModels;
using RN.ERP.DAL.Context;
using RN.ERP.DAL.Common;
using System.Data.SqlClient;
using System.Data;

namespace RN.ERP.DAL.LcDAL
{
    public class EnterpriseDALWh
    {
        ZBDL_Context dbContext = new ZBDL_Context();

        /// <summary>
        /// 公共:查询企业信息列表
        /// </summary>
        /// <param name="pageCount"></param>
        /// <param name="pageSize"></param>
        /// <param name="conditions"></param>
        /// <returns></returns>
        public List<Tb_Corp_CompanyInfo_Zbdl> GetCorpInfos(int pageIndex, int pageSize, string condition)
        {
            var list = new List<Tb_Corp_CompanyInfo_Zbdl>();
            string sql = "SELECT * From tb_Corp_CompanyInfo C left join T_Sys_Users T On C.CorpUserGuid=T.UserGuid where 1=1 ";
            sql += (string.IsNullOrEmpty(condition) ? string.Empty : condition);
            sql += "order by C.id desc";
            list = dbContext.Database.SqlQuery<Tb_Corp_CompanyInfo_Zbdl>(sql).Skip((pageIndex - 1) * pageSize).Take(pageSize).ToList();
            return list;
        }

        /// <summary>
        /// 公共:查询企业审核信息列表
        /// </summary>
        /// <param name="pageCount"></param>
        /// <param name="pageSize"></param>
        /// <param name="conditions"></param>
        /// <returns></returns>
        public List<Tb_Corp_CompanyInfo_Zbdl> GetFlowCorpInfos(int pageIndex, int pageSize, string condition)
        {
            var list = new List<Tb_Corp_CompanyInfo_Zbdl>();
            string sql = @"SELECT * From tb_Corp_CompanyInfo 
                             left join T_Sys_Users  On tb_Corp_CompanyInfo.CorpUserGuid=T_Sys_Users.UserGuid 
                             left join Tb_FlowCode  On CONVERT(nvarchar(50),tb_Corp_CompanyInfo.CorpUserGuid) =Tb_FlowCode.ObjectId
                             where Tb_FlowCode.CodeType = '8001' AND ( Tb_FlowCode.FlowCode ='0,019,022' or Tb_FlowCode.FlowCode ='0,019,022,' or tb_FlowCode.FlowCode ='0,019,' or tb_FlowCode.FlowCode ='0,019' ) ";
            sql += (string.IsNullOrEmpty(condition) ? string.Empty : condition);
            sql += " order by tb_Corp_CompanyInfo.id desc";
            list = dbContext.Database.SqlQuery<Tb_Corp_CompanyInfo_Zbdl>(sql).Skip((pageIndex - 1) * pageSize).Take(pageSize).ToList();
            return list;
        }

        /// <summary>
        /// 查询企业个数
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="condition"></param>
        /// <returns></returns>
        public int GetCorpInfosCount(int pageIndex, int pageSize, string condition)
        {
            int num = 0;

            string sql = "SELECT * From tb_Corp_CompanyInfo as c where 1=1 ";
            sql += (string.IsNullOrEmpty(condition) ? string.Empty : condition);
            sql += " order by id desc";
            num = dbContext.Database.SqlQuery<Tb_Corp_CompanyInfo_Zbdl>(sql).Count();
            return num;
        }

        /// <summary>
        /// 公共:查询企业详细信息
        /// </summary>
        /// <param name="conditions"></param>
        /// <returns></returns>
        public Tb_Corp_CompanyInfo_Zbdl GetCompanyInfo(string conditions)
        {
            string sql = "SELECT * From tb_Corp_CompanyInfo where 1=1 ";
            sql += (string.IsNullOrEmpty(conditions) ? string.Empty : conditions);
            Tb_Corp_CompanyInfo_Zbdl dbCorpComp = dbContext.Database.SqlQuery<Tb_Corp_CompanyInfo_Zbdl>(sql).FirstOrDefault();
            return dbCorpComp;
        }

        /// <summary>
        /// 公共:查询企业资质
        /// </summary>
        /// <param name="corpUserGuid"></param>
        /// <param name="conditions"></param>
        /// <returns></returns>
        public IList<tb_Corp_Quality_Zbdlry> GetCompanyQualities(string corpUserGuid, string conditions)
        {
            var list = new List<tb_Corp_Quality_Zbdlry>();
            string sql = String.Format("SELECT * From tb_Corp_Quality  where CorpUserGuid= '{0}' and IsDel = 0 ", corpUserGuid);
            sql += (string.IsNullOrEmpty(conditions) ? string.Empty : conditions);
            sql += "order by id desc";
            list = dbContext.Database.SqlQuery<tb_Corp_Quality_Zbdlry>(sql).ToList();
            return list;
        }

        /// <summary>
        /// 公共:根据企业组织机构代码查询企业占用情况
        /// CorpRegisterValidationModel类包含bool的是否已注册，如果已经注册则包含Tb_Corp_CompanyInfo信息，和string的说明信息。
        /// </summary>
        /// <param name="corpCode"></param>
        /// <returns></returns>
        public CorpRegisterValidationModel CorpRegisterValidate(string corpCode)
        {
            CorpRegisterValidationModel corpReg = new CorpRegisterValidationModel();
            //tb_Corp_CompanyInfo_ZBDL dbCorpComp = dbContext.Corp_CompanyInfos.FirstOrDefault(p => p.OrginazationCode == corpCode && p.IsDel == false);
            //corpReg.CorpInfo = dbCorpComp;
            //if (dbCorpComp != null)
            //{
            //    corpReg.IsRegisted = true;
            //    corpReg.Message = "已注册";
            //}
            //else
            //{
            //    corpReg.IsRegisted = false;
            //    corpReg.Message = "未注册";
            //}
            return corpReg;
        }

        public List<RN.ERP.Entity.CorpBzjInfo> GetCorBzjList(out int count, int pageIndex, int pageSize, string condition, string areaCode)
        {
            var list = new List<RN.ERP.Entity.CorpBzjInfo>();
            string sqlCount = "select DISTINCT p.ID from zb_CorpBzjInfo bzj inner join zb_ProjectBidInfo b on b.ID=bzj.bidID inner join zb_ProjectInfo p on b.treeID=p.ID  where cmb_Flag=4";
            string sql = "select bzj.CorpName,p.ProjectName,p.ID as pID,b.BidName,b.ID as bID from zb_CorpBzjInfo bzj inner join zb_ProjectBidInfo b on b.ID=bzj.bidID inner join zb_ProjectInfo p on b.treeID=p.ID where cmb_Flag=4";
            if (!string.IsNullOrEmpty(condition))
            {
                sql += "and BZJBack_Date>='" + condition + " 0:00:00'and BZJBack_Date<'" + condition + " 23:59:59'";
            }
            sql += "order by BZJBack_Date DESC";
            var listCount = new List<int>();
            listCount = dbContext.Database.SqlQuery<int>(sqlCount).ToList();
            count = listCount.Count;
            list = dbContext.Database.SqlQuery<RN.ERP.Entity.CorpBzjInfo>(sql).ToList();
            return list;

        }

        public List<string> GetCorBzjGroupList(out int count, int pageIndex, int pageSize, string conditon, string areaCode)
        {
            var list = new List<string>();
            string sql = @"select  Back_Date from (
 SELECT Convert(char(10),BZJBack_Date,120) as Back_Date,* FROM zb_CorpBzjInfo where cmb_Flag=4 ";
            if (!string.IsNullOrEmpty(conditon))
            {
                sql += "and BZJBack_Date>='" + conditon + " 0:00:00'and BZJBack_Date<'" + conditon + " 23:59:59'";
            }
            sql += @" )a  
group by Back_Date order by Back_Date DESC";

            // var query = dbContext.Database.SqlQuery<DateTime>(sql);
            list = dbContext.Database.SqlQuery<string>(sql).ToList();
            var listPage = dbContext.Database.SqlQuery<string>(sql).Skip((pageIndex - 1) * pageSize).Take(pageSize).ToList();
            count = list.Count;
            // list = query.ToList();
            return listPage;
        }

        public void GenerateFlowCodebyAppId(string userGuid, int appID, string areaCode)
        {
            dbContext.Database.ExecuteSqlCommand("exec GenerateFlowCodebyAppId @UserGuid,@AppID,@AreaCode", new SqlParameter[] { 
                    new SqlParameter("@UserGuid",userGuid),
                    new SqlParameter("@AppID",appID),
                    new SqlParameter("@AreaCode",areaCode)
                });
        }


        /// <summary>
        /// 根据企业或者人员的Guid获取所在企业RoleID
        /// </summary>
        /// <param name="objectid"></param>
        /// <returns></returns>
        public int GetRoleIDbyGuid(string objectid)
        {
            int roleID = 0;
            var list = new List<T_Sys_Users_Zbdl>();
            SqlParameter[] sps = new SqlParameter[]
            {
                new SqlParameter("Guid",objectid)
            };
            string sql = String.Format("exec sp_GetRoleIDbyGuid  @Guid");
            roleID = dbContext.Database.SqlQuery<int>(sql, sps).FirstOrDefault();
            return roleID;
        }

        /// <summary>
        /// 从excel中获得待入库的企业信息
        /// </summary>
        /// <param name="uploadPath"></param>
        /// <returns></returns>
        public List<Tb_Corp_CompanyInfo_Zbdl> GetCompanyInfoByExcel(string uploadPath, out string msg)
        {
            var list = new List<Tb_Corp_CompanyInfo_Zbdl>();
            Tb_Corp_CompanyInfo_Zbdl corp = null;

            try
            {
                DataTable dt = new CommonHelper().GetExcelTable(uploadPath);

                foreach (DataRow row in dt.Rows)
                {
                    corp = new Tb_Corp_CompanyInfo_Zbdl();
                    corp.CorpName = row[0].ToString();
                    corp.OrginazationCode = row[2].ToString();
                    corp.Address = row[4].ToString();
                    corp.LinkMan1 = row[5].ToString();
                    corp.Tel1 = row[6].ToString();
                    list.Add(corp);
                }
                msg = "true";
            }
            catch (Exception e)
            {
                msg = e.Message;
                throw;
            }

            return list;
        }

       
         
    }
}
